package com.hylanda.pb.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.hylanda.pb.entity.TableSql;

/** 
 * @author zhangy
 * @E-mail:blackoon88@gmail.com 
 * @qq:846579287
 * @version created at：2018年2月23日 上午9:50:21 
 * note
 */
@Repository
public class TableSqlDao extends SqlServerBaseDao {
	
	public JdbcTemplate getTemplate(){
		return super.template;
	}
	/*
	 * 本地sql查询,注意表名啥的都用数据库中的名称，适用于特定数据库的查询
	 */
	//SELECT TOP ?1 id FROM(SELECT ROW_NUMBER () OVER (ORDER BY id ASC) RowNumber ,* FROM sys_report_files) A WHERE A.RowNumber > (?2 - 1) * ?1

//	public Page<TableSql> findPage(TableSqlQo tableSqlQo) {
//		Pageable pageable = new PageRequest(tableSqlQo.getPage(), tableSqlQo.getSize(),Sort.Direction.DESC, "id");
//		int minRowNumber=(tableSqlQo.getPage())*tableSqlQo.getSize();
//		String sql="SELECT TOP "+tableSqlQo.getSize()+" id,tableName,tableCname,createTime,isEnable,sort FROM(SELECT ROW_NUMBER () OVER (ORDER BY id DESC) RowNumber ,* FROM sys_table_sql ) A WHERE A.RowNumber >"+minRowNumber;
//		if(!StringUtils.isEmpty(tableSqlQo.getTableName())){
//			sql="SELECT TOP "+tableSqlQo.getSize()+" id,tableName,tableCname,createTime,isEnable,sort FROM(SELECT ROW_NUMBER () OVER (ORDER BY id DESC) RowNumber ,* FROM sys_table_sql where tableName like'%"+tableSqlQo.getTableName()+"%') A WHERE A.RowNumber >"+minRowNumber;
//		}
//		List<Map<String,Object>> list=super.template.queryForList(sql);
//		List<TableSql> ll=new ArrayList<>();
//		for(Map<String,Object> map:list){
//			TableSql rf=new TableSql();
//			rf.setId((Long)map.get("id"));
//			rf.setTableName((String)map.get("tableName"));
//			rf.setTableCname((String)map.get("tableCname"));
//			rf.setIsEnable((Integer)map.get("isEnable"));
//			rf.setSort((Long)map.get("sort"));
////			rf.setSql((String)map.get("sql"));
//			rf.setCreateTime((Timestamp)map.get("createTime"));
//			ll.add(rf);
//		}
//		int total =queryCount("select count(*) from sys_table_sql");
//		return new PageImpl<TableSql>(ll, pageable, total);
//	}
	
	
	public int update(TableSql entity) {
		String strSql="update sys_table_sql set tableName=?,tableCname=?,createTime=?,isEnable=?,sort=?,sql=? where id=?";
		int i=super.template.update(strSql, new Object[]{entity.getTableName(),entity.getTableCname(),new Timestamp(System.currentTimeMillis()),entity.getIsEnable(),entity.getSort(),entity.getSql(),entity.getId()});
		return i;
	}
	
	public int delete(Long id) {
		String sql="delete from sys_table_sql where id=?";
		return super.template.update(sql, new Object[]{id});
	}

	public TableSql save(final TableSql entity) {
		 KeyHolder keyHolder = new GeneratedKeyHolder(); 
		 super.template.update(new PreparedStatementCreator(){  
             public PreparedStatement createPreparedStatement(Connection conn) throws SQLException{  
                 int i = 0;  
                 String strSql="insert into sys_table_sql (tableName,tableCname,createTime,isEnable,sort,sql) values (?,?,?,?,?,?)";
                 PreparedStatement ps = conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);  
                 ps.setString(++i,entity.getTableName());
                 ps.setString(++i, entity.getTableCname());
                 Timestamp time=new Timestamp(System.currentTimeMillis());
                 ps.setTimestamp(++i,time);
                 ps.setInt(++i, 1);
                 ps.setLong(++i, entity.getSort());
                 ps.setString(++i, entity.getSql());
                 return ps;
             }  
         },keyHolder);
		 long pid= keyHolder.getKey().longValue();
		 entity.setId(pid);
		 return entity;
	}
	
	public TableSql findEntity(Long id) {
		String sql="select id,tableName,tableCname,createTime,isEnable,sort,sql from sys_table_sql where id=?";
		Map<String,Object> map=super.template.queryForMap(sql, new Object[]{id});
		TableSql rf=new TableSql();
		rf.setId((Long)map.get("id"));
		rf.setTableName((String)map.get("tableName"));
		rf.setTableCname((String)map.get("tableCname"));
		rf.setIsEnable((Integer)map.get("isEnable"));
		rf.setSort((Long)map.get("sort"));
		rf.setSql((String)map.get("sql"));
		rf.setCreateTime((Timestamp)map.get("createTime"));
		return rf;
	}
	
	public String findOne(Long id) {
		String sql="select tableName from sys_table_sql where id=?";
		return super.template.queryForObject(sql,String.class, new Object[]{id});
	}

	public String findByName(String name) {
		String sql="select tableName from sys_table_sql where tableName=?";
		List<String> list=super.template.queryForList(sql, new Object[]{name}, String.class);
		if(list==null||list.size()==0){
			return null;
		}
		return list.get(0);
	}

	
}
